﻿--提取职级异动二级审批列表
CREATE proc [dbo].[proc_Employee_GradeMove_GetApplySList]
(
	@startIndex int,
	@endIndex int,
	@docount bit,
	@ename nvarchar(50),
	@companyid nvarchar(4),
	@audit nvarchar(4)
)
as

set nocount on
	if(@docount=1)
		select *,'EName' as EName,'Sex' as Sex,'CompanyName' as CompanyName,'DName'as DName,'GradeName' as GradeName,'PostName' as PostName from employee_Grade EE inner join Employee E on EE.EId=E.Id 
		where
				FirstAuditPerson <> ''
			 and e.ename like '%'+@ename+'%'
			 and e.companyid like @companyid 
			 and ee.Proposer is not null
			 and (case when ee.Secondaudit=1 then '1' 
				   when ee.Secondaudit=0 and ee.Secondauditperson is null then '0'
				   when ee.Secondaudit=0 and ee.Secondauditperson is not null then '1' end) like '%'+@audit+'%'
			and  e.state <> 5
	else
	begin
	declare @indextable table(id int identity(1,1),nid int)
	set rowcount @endIndex
	insert into @indextable(nid) select EE.id from employee_Grade EE inner join Employee E on EE.EId=E.Id 
	where
				FirstAuditPerson <> ''
			 and e.ename like '%'+@ename+'%'
			 and e.companyid like @companyid 
			 and ee.Proposer is not null
			 and (case when ee.Secondaudit=1 then '1' 
				   when ee.Secondaudit=0 and ee.Secondauditperson is null then '0'
				   when ee.Secondaudit=0 and ee.Secondauditperson is not null then '1' end) like '%'+@audit+'%'
			and  e.state <> 5
	 order by EE.id asc
	select 
		EE.Id,
		(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
		(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
		EE.EId,
		(select EName from Employee E where EE.EId=E.Id) as EName,
		(select Sex from Employee E where EE.EId=E.Id) as Sex,
		(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,
		(select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,
		(select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,
		--(select Title from Grade G where id=(select GradeId from employee E where EE.EId=E.Id)) as GradeName,
		(select Title from Grade G where id=ee.AGradeId) as GradeName,
		EE.FirstAudit,
		EE.FirstAuditPerson,
		EE.FirstAuditTime,
		EE.SecondAudit,
		EE.SecondAuditPerson,
		EE.SecondAuditTime
	from employee_Grade EE
	inner join @indextable t on 
	EE.id=t.nid
	where 
		t.id between @startIndex and @endIndex 
	order by t.id
	end
set nocount off
RETURN